﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 台签打印
{
    class DB
    {

        public static string[] GetTables(string dbFileName)
        {
            string connString = GetconnString(dbFileName);
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                return schemaTable.AsEnumerable().Select(x => x.Field<string>("TABLE_NAME")).ToArray();
            }
        }


        public static DataTable GetDt(string dbFileName, string sql)
        {
            DataTable dt = new DataTable();
            string connString = GetconnString(dbFileName);
            using (var conn = new OleDbConnection(connString))
            {
                conn.Open();
                using (var adapter = new OleDbDataAdapter(sql, conn))
                {
                    adapter.Fill(dt);
                }
            }
            return dt;
        }


        public static int ExecuteNonQuery(string dbFileName, string sql)
        {
            int result = 0;
            string connectionString = GetconnString(dbFileName);
            using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString))
            {
                oleDbConnection.Open();
                result = new OleDbCommand(sql, oleDbConnection).ExecuteNonQuery();
            }
            return result;
        }



        private static string GetconnString(string dbFile)
        {
            string connString = null;
            switch (Path.GetExtension(dbFile))
            {
                case ".xls":   //xls 连接字符串 HDR=YES表头 IMEX=1读、2写
                    connString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={dbFile};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\";";
                    break;
                //goto case ".xlsx";

                case ".xlsx":   //xlsx 连接字符串 ；xls 也可以
                    connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbFile};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\";";
                    break;

                case ".xlsm":    //同 .xlsx
                    goto case ".xlsx";

                case ".csv":    //csv,txt 连接字符串,数据源为文件夹  FMT=Delimited 逗号分隔
                    dbFile = Path.GetDirectoryName(dbFile);
                    connString = $"Provider=Microsoft.ACE.OLEDB.12.0;;Data Source={dbFile};Extended Properties=\"text;HDR=YES;FMT=Delimited\";";
                    break;

                case ".txt":    //同csv
                    goto case ".csv";

                case ".mdb":    //mdb 连接字符串
                    connString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={dbFile};";
                    break;
            }
            return connString;
        }
    }
}
